# coding:utf-8
"""
    @project: automated_testing_platform
    @Author：caicaizi
    @file： excel_executor.py
    @date：2023/6/3 14:24
"""
from pathlib import Path
from openpyxl import load_workbook, Workbook
from openpyxl.workbook.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell.cell import Cell
from openpyxl.styles import Border, Side, Font, Alignment, Color

p = Path.cwd()
print(p)


class ExcelExecutor:
    def __init__(self):
        self.workbook: Workbook = None
        self.filepath = None
        self.font = Font(color=None)

    def load_workbook(self, filepath: str) -> Workbook:
        try:
            self.workbook = load_workbook(filepath)
        except Exception as e:
            raise e
        self.filepath = filepath
        return self.workbook

    def create_workbook(self, filepath: str) -> Workbook:
        try:
            self.workbook = Workbook()
        except Exception as e:
            raise e
        self.filepath = filepath
        return self.workbook

    def save_workbook(self, filepath: str):
        try:
            self.workbook.save(filepath)
        except Exception as e:
            raise e

    def close_workbook(self):
        try:
            self.workbook.close()
        except Exception as e:
            raise e

    def create_sheet(self, title: str = None, index: int = None) -> Worksheet:
        try:
            sheet = self.workbook.create_sheet()
            return sheet
        except Exception as e:
            raise e

    def remove_sheet(self, sheet: Worksheet):
        try:
            self.workbook.remove(sheet)
        except Exception as e:
            raise e

    def get_active_sheet(self) -> Worksheet:
        sheet = self.workbook.active
        return sheet

    def get_sheet_by_name(self, sheet_name: str) -> Worksheet:
        try:
            sheet = self.workbook[sheet_name]
            return sheet
        except Exception as e:
            raise e

    def get_sheet_by_index(self, sheet_index: int) -> Worksheet:
        try:
            sheet_name = self.workbook.get_sheet_names()[sheet_index]
        except Exception as e:
            raise e
        sheet = self.workbook[sheet_name]
        return sheet

    def get_max_row_num(self, sheet: Worksheet):
        return sheet.max_row

    def get_max_col_num(self, sheet: Worksheet):
        return sheet.max_column

    def get_min_row_num(self, sheet: Worksheet):
        return sheet.min_row

    def get_min_col_num(self, sheet: Worksheet):
        return sheet.min_column

    def get_row(self, sheet: Worksheet, row_num: int):
        try:
            return list(sheet.rows)[row_num - 1]
        except Exception as e:
            raise e

    def get_col(self, sheet: Worksheet, col_num: int):
        try:
            return list(sheet.columns)[col_num - 1]
        except Exception as e:
            raise e

    def get_cell(self, sheet: Worksheet, row_num: int = None, col_num: int = None) -> Cell:
        try:
            return sheet.cell(row_num, col_num)
        except Exception as e:
            raise e

    def get_cell_value(self, sheet: Worksheet, row_num: int = None, col_num: int = None):
        try:
            return sheet.cell(row_num, col_num).value
        except Exception as e:
            raise e

    def write_to_cell(self, sheet: Worksheet, row_num: int = None, col_num: int = None, value=None, style=None):
        try:
            cell = sheet.cell(row_num, col_num)
            cell.value = value
            if style is not None:
                pass
            self.workbook.save(self.filepath)
        except Exception as e:
            raise e


if __name__ == '__main__':
    ee = ExcelExecutor()
    ee.load_workbook('demo.xlsx')
    print("通过名称获取sheet对象的名字：", ee.get_sheet_by_name('测试用例1').title)
    # print("通过index序号获取sheet对象的名字：", ee.get_sheet_by_index(0).title)
    sheet = ee.get_sheet_by_name('搜索')
    print(ee.get_max_row_num(sheet))
    print(ee.get_max_col_num(sheet))
    rows = ee.get_row(sheet, 2)
    for row in rows:
        print(row.value)
    print(ee.get_cell_value(sheet, 1, 1))
    ee.write_to_cell(sheet, 5, 5, '我爱宝宝')
